﻿using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Architecture.Repository
{
    public class DBHelper
    {
        private string ConnetionString = "";
        public DBHelper(IConfiguration _configuration)
        {
            ConnetionString = _configuration.GetConnectionString("ConnectionString");
        }



        /// <summary>
        /// 公共查询的方法
        /// </summary>
        /// <param name="sqlCmd">执行的sql命令</param>
        /// <param name="values">sql参数化对象</param>
        /// <returns>查询的DataSet结果集</returns>
        public async Task<DataSet> Search(string sqlCmd, SqlParameter[] values = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnetionString))
            {
                try
                {
                    //创建连接对象
                    conn.Open();//打开连接
                    SqlCommand command = new SqlCommand(sqlCmd, conn);//执行sql命令对象 传入要执行的sql语句和数据连接对象
                    if (values != null)
                    {
                        command.Parameters.AddRange(values);
                    }
                    //数据适配器
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    //创建数据集，将适配器查询回来的数据放入数据集中
                    DataSet set = new DataSet();
                    adapter.Fill(set);
                    return await Task.FromResult(set);
                }
                catch (Exception ee)
                {
                    //写异常处理日志
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }

        }


        public async Task<List<T>> GetListDataBySqlWhere<T>(string sqlCmd, SqlParameter[] values = null) where T : class, new()
        {
            DataSet set = await Search(sqlCmd, values);

            return ConvertDataTableToList<T>(set.Tables[0]);
        }
        /// <summary>
        /// 根据Sql命令返回单行单列的值
        /// </summary>
        /// <param name="sqlCmd">sql命令</param>
        /// <param name="values">参数化对象集合</param>
        /// <returns>返回任意类型的值</returns>
        public async Task<object> ExecuteScalar(string sqlCmd, SqlParameter[] values = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnetionString))
            {
                try
                {
                    //创建连接对象
                    conn.Open();//打开连接
                    SqlCommand command = new SqlCommand(sqlCmd, conn);//执行sql命令对象 传入要执行的sql语句和数据连接对象
                    if (values != null)
                    {
                        command.Parameters.AddRange(values);
                    }
                    object value = await command.ExecuteScalarAsync();
                    return value;
                }
                catch (Exception ee)
                {
                    //写异常处理日志
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 根据Sql命令返回单行单列的值
        /// </summary>
        /// <param name="sqlCmd">sql命令</param>
        /// <param name="values">参数化对象集合</param>
        /// <returns>返回任意类型的值</returns>
        public async Task<int> ExecuteNonQueryAsync(string sqlCmd, SqlParameter[] values = null)
        {
            using (SqlConnection conn = new SqlConnection(ConnetionString))
            {
                try
                {
                    //创建连接对象
                    conn.Open();//打开连接
                    SqlCommand command = new SqlCommand(sqlCmd, conn);//执行sql命令对象 传入要执行的sql语句和数据连接对象
                    if (values != null)
                    {
                        command.Parameters.AddRange(values);
                    }
                    int value = await command.ExecuteNonQueryAsync();
                    return value;
                }
                catch (Exception ee)
                {
                    //写异常处理日志
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 将DataTable转List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>

        public List<T> ConvertDataTableToList<T>(DataTable dataTable) where T : class, new()
        {
            List<T> list = new List<T>();
            foreach (DataRow row in dataTable.Rows)
            {
                T obj = new T();

                foreach (DataColumn col in dataTable.Columns)
                {
                    PropertyInfo propertyInfo = obj.GetType().GetProperty(col.ColumnName);
                    if (propertyInfo != null && row[col] != DBNull.Value)
                    {
                        propertyInfo.SetValue(obj, row[col], null);
                    }
                }
                list.Add(obj);
            }
            return list;
        }

    }
}
